﻿using System;
using System.Collections.Generic;
using System.Text;
using SdmxMl.Excel;

namespace SasPlugIn.DB
{
    /// <summary>
    /// Used for generation of instructions to create a Star Schema in oracle.
    /// based on DSD metadata.
    /// </summary>
    public class StarSchemeOraGenerator : OracleTableGenerator
    {
        #region Constants

        const string FACT_TABLE_PREFIX = "FACT_";
        const string DIM_TABLE_PREFIX = "DIM_";
        const string AMPERCENT_NO_PARAMETER = "set define off;";

        #endregion

        #region Internal Members

        private DataStructureDefTable dsdDefTable;
        private List<DimOptions> proposedDimOptions;
        private StarSchemeParameters schemeParam;
        private List<DbColumn> headerList;

        #endregion

        #region Constructor

        /// <summary> Constructor </summary>
        /// <param name="definitionTable">the DSD definitions as a table</param>
        /// <param name="dimOptionsList">Infoa aboud dim table name and annotations to manage when generating</param>
        /// <param name="param">Parameters used for Oracle schema code generation</param>
        public StarSchemeOraGenerator(DataStructureDefTable definitionTable, List<DimOptions> dimOptionsList, StarSchemeParameters param) : 
            base (definitionTable, param.TableSpaceName)
        {
            dsdDefTable = definitionTable;
            dsdDefTable.Name = FACT_TABLE_PREFIX + dsdDefTable.Name;
            proposedDimOptions = dimOptionsList == null ? new List<DimOptions>() : dimOptionsList;
            schemeParam = param == null ? new StarSchemeParameters() : param;
            headerList = new List<DbColumn>(dsdDefTable.Columns);
        }
        #endregion

        #region Header helpers

        /// <summary>
        /// Rearder columns order according to parameters provided list
        /// </summary>
        /// <returns>a comma separated string defining unknown columns and not in list columns</returns>
        public string ReorderHeader()
        {
            StringBuilder sb = new StringBuilder();

            List<DbColumn> workList = new List<DbColumn>(headerList);
            List<DbColumn> orderList = new List<DbColumn>();
            foreach (CsvHeaderColumn csvHeadCol in schemeParam.HeaderOrder)
            {
                DbColumn dbCol = workList.Find(x => x.Name.ToUpper() == csvHeadCol.Name);
                if (dbCol == null)
                {
                    if (sb.Length == 0)
                        sb.Append("Unknown column(s) ID: ");
                    else
                        sb.Append(", ");
                    sb.Append(csvHeadCol.Name);
                }
                else
                {
                    orderList.Add(dbCol);
                    workList.Remove(dbCol);
                }
            }
            if (workList.Count > 0)
            {
                sb.Append(Environment.NewLine).Append("Column(s) not in reorder list: ");
                foreach (DbColumn col in workList)
                {
                    sb.Append(col.Name).Append(", ");
                   // orderList.Add(col);
                }
            }

            if (sb.Length == 0)
                headerList = orderList;
            
            return sb.ToString();
        }
        #endregion

        #region Public generation method

        /// <summary> Star schema generation </summary>
        /// <returns>Stringbuilder holding Oracle star schema generation code</returns>
        public override StringBuilder GenerateTable()
        {
            sb = new StringBuilder(1024);

            // Avoid char & to act as a parameter demand
            sb.Append(AMPERCENT_NO_PARAMETER).Append(Environment.NewLine);

            // Imposes another column order
            if (schemeParam.HeaderOrder.Count > 0)
            {
                string s = ReorderHeader();
                if (s.Length > 0)
                    throw new ApplicationException(s);
            }

            // create dim, activates only dimensions proposed in list
            List<Constraint> constraints = new List<Constraint>();

            int dimIndex = 1;
            foreach (DimOptions dim in proposedDimOptions)
            {
                if (dsdDefTable.BindedCodelists.ContainsKey(dim.FullId))
                {
                    // Restrict to working header cases
                    string columnName = dsdDefTable.BindedCodelists[dim.FullId].ColName;
                    if (headerList.Find(x => x.Name.ToUpper() == columnName.ToUpper()) != null)
                    {
                        // Generate Oracle code for DIM, use table renaming and annotation inventory provided in dimOption
                        CodelistTable tbcl = new CodelistTable(dsdDefTable.BindedCodelists[dim.FullId].GetCodelist(),
                            dsdDefTable.BindedCodelists[dim.FullId].TFormat, dim.AnnotInventory,
                            DIM_TABLE_PREFIX + dim.TableName, dsdDefTable.Prefix);
                        OracleTableGenerator tbGen = new OracleTableGenerator(tbcl, schemeParam.TableSpaceName);
                        sb.Append(Environment.NewLine);
                        sb.Append(tbGen.GenerateTable()).ToString();
                        tbGen.CreateUniqueKey(sb, dimIndex++);
                        sb.Append(Environment.NewLine);

                        constraints.Add(new Constraint(columnName, tbcl.Name));
                    }
                }
            }


            // Table Drop / Create
            CreateTable(table.Name);

            // Columns creation
            StartColumns();
            foreach (DbColumn col in headerList)
            {
                GenerateColumn(sb, col);
            }

            // Constraint generation for each Var having a DIM table generated

            string baseKeyConstaint = "FK_" + dsdDefTable.Name + "_";
            for (int i = 0; i < constraints.Count; i++)
            {
                sb.Append("CONSTRAINT ").Append(baseKeyConstaint).Append(i);
                sb.Append(" FOREIGN KEY (").Append(constraints[i].ColumnName).Append(")").Append(Environment.NewLine);
                if (string.IsNullOrEmpty(dsdDefTable.Prefix))
                    sb.Append("  REFERENCES ").Append(constraints[i].DimTableName).Append(" (CODE) DISABLE,").Append(Environment.NewLine);
                else
                    sb.Append("  REFERENCES ").Append(dsdDefTable.Prefix).Append('.').Append(constraints[i].DimTableName).Append(" (CODE) DISABLE,").Append(Environment.NewLine);
            }
           
            FinalizeColumns();

            // Bitmap Indexes
            string baseBitIndex = "BIDX_" + dsdDefTable.Name + "_";
            for (int i = 0; i < constraints.Count; i++)
            {
                sb.Append("CREATE BITMAP INDEX ").Append(baseBitIndex).Append(constraints[i].ColumnName.ToUpper()).Append(" ON ");
                sb.Append(TableFullName).Append(" (").Append(constraints[i].ColumnName.ToUpper()).Append(");").Append(Environment.NewLine);
           }

            CreateExternalTable(sb);

            return sb;
        }
        #endregion

        #region External table support

        private StringBuilder CreateExternalTable(StringBuilder sb)
        {
            // Repeat table create instructions for  External
            // Table Drop / Create
            string factTableName = table.Name;
            table.Name = "EXT_" + table.Name;
            CreateTable(table.Name);

            // Columns creation
            StartColumns();
            foreach (DbColumn col in headerList)
            {
                GenerateColumn(sb, col);
            }
            sb.Remove(sb.Length - 3, 3);
            sb.Append(") ").Append(Environment.NewLine);


            // Organisation instructions
            sb.Append("ORGANIZATION EXTERNAL").Append(Environment.NewLine);
            sb.Append("( TYPE ORACLE_LOADER").Append(Environment.NewLine);
            sb.Append(" DEFAULT DIRECTORY ").Append(schemeParam.DefaultExtDirectoryName).Append(Environment.NewLine);
            sb.Append("  ACCESS PARAMETERS").Append(Environment.NewLine);
            sb.Append("     ( RECORDS DELIMITED BY NEWLINE").Append(Environment.NewLine);
            sb.Append("             CHARACTERSET ").Append(schemeParam.CharacterSet).Append(Environment.NewLine);
            sb.Append("             STRING SIZES ARE IN CHARACTERS").Append(Environment.NewLine);
            sb.Append("             BADFILE ").Append(schemeParam.DefaultExtDirectoryName).Append(":'").Append(dsdDefTable.Name).Append(".BAD'").Append(Environment.NewLine);
            sb.Append("             DISCARDFILE ").Append(schemeParam.DefaultExtDirectoryName).Append(":'").Append(dsdDefTable.Name).Append(".DSC'").Append(Environment.NewLine);
            sb.Append("             LOGFILE  ").Append(schemeParam.DefaultExtDirectoryName).Append(":'").Append(dsdDefTable.Name).Append(".LOG'").Append(Environment.NewLine);
            sb.Append("             SKIP 1").Append(Environment.NewLine);
            sb.Append("             FIELDS TERMINATED BY '").Append(schemeParam.CsvSeparator).Append("'").Append(Environment.NewLine);
            sb.Append("             OPTIONALLY ENCLOSED BY '\"' AND '\"'").Append(Environment.NewLine);
            sb.Append("             LRTRIM").Append(Environment.NewLine);
            sb.Append("        ( ").Append(Environment.NewLine);

            //Iterate columns
            foreach (DbColumn col in headerList)
            {
                sb.Append(col.Name).Append(',').Append(Environment.NewLine);
            }
            sb.Remove(sb.Length - 3, 3);

            sb.Append("    ) )").Append(Environment.NewLine);
            sb.Append("      LOCATION (").Append(schemeParam.DefaultExtDirectoryName).Append(":'").Append(schemeParam.CsvFileNameName).Append("')").Append(Environment.NewLine);
            sb.Append("    )").Append(Environment.NewLine);
            sb.Append("   REJECT LIMIT 100 ").Append(Environment.NewLine);
            sb.Append("  PARALLEL ;").Append(Environment.NewLine);

            // Select / Truncate
            sb.Append("SELECT '").Append(dsdDefTable.Name).Append("' AS TABLE_NAME, COUNT(*) AS CPT_LINES FROM ").Append(dsdDefTable.Name).Append(";");
            sb.Append(Environment.NewLine);
            sb.Append("TRUNCATE TABLE ").Append(factTableName).Append(";").Append(Environment.NewLine);

            sb.Append("--INSERT /* +APPEND */ INTO ").Append(factTableName).Append(" SELECT * FROM ").Append(dsdDefTable.Name).Append(";").Append(Environment.NewLine);
            sb.Append("SELECT '").Append(factTableName).Append("', COUNT(*) FROM ").Append(factTableName).Append(";").Append(Environment.NewLine);
            return sb;
        }

        #endregion
    }

    #region Dim option class

    /// <summary>
    /// Class for holding an generation instruction set in grid
    /// </summary>
    public class DimOptions
    {
        /// <summary> Dim codelist SDMX full id </summary>
        public string FullId { get; private set; }

        /// <summary> Table name redefined in grid cell </summary>
        public string TableName { get; private set; }

        /// <summary> Optionnal Annotation columns to generate for the dim </summary>
        public AnnotationInventory AnnotInventory { get; private set; }

        public DimOptions(string codelistFullId, string tableName, AnnotationInventory anInventory)
        {
            FullId = codelistFullId;
            TableName = tableName;
            AnnotInventory = anInventory;
        }
    }
    #endregion

    #region StarScheme parameters class

    /// <summary>
    /// Configuration parameters for generating star schema
    /// </summary>
    [Serializable]
    public class StarSchemeParameters
    {
        public string DefaultExtDirectoryName { get; set; }
        public string CsvFileNameName { get; set; }
        public string TableSpaceName { get; set; }
        public string CharacterSet { get; set; }
        public char CsvSeparator { get; set; }
        public List<CsvHeaderColumn> HeaderOrder { get; set; }

        public StarSchemeParameters()
        {
            DefaultExtDirectoryName = "DIR_EXT_TABLES";
            TableSpaceName = "DWHWA2_DATA";
            CharacterSet = "WE8ISO8859P15";
            CsvSeparator = ';';
            HeaderOrder = new List<CsvHeaderColumn>();
        }
    }
    #endregion

    /// <summary> Helper class for redefining columns order in header/ </summary>
    public class CsvHeaderColumn
    {
        public string Name { get; set; }

        public CsvHeaderColumn()
        {
            Name = "undefined";
        }

        public CsvHeaderColumn(string hName)
        {
            Name = hName;
        }

        public override string ToString()
        {
            return Name;
        }
    }

    public class Constraint
    {
        public string ColumnName { get; set; }
        public string DimTableName { get; set; }
        public Constraint(string colName, string dimName)
        {
            ColumnName = colName;
            DimTableName = dimName;
        }
    }


}
